package com.rx.substation.controller;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Clock;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.rx.substation.dao.CalTotalScoreDAO;
import com.rx.substation.dao.MonthQCInspectionDAO;
import com.rx.substation.dao.MonthRunUnitQCDAO;
import com.rx.substation.dao.MonthRunUnitRunningDAO;
import com.rx.substation.dao.MonthStationRunningDAO;
import com.rx.substation.dao.MonthValidateDAO;
import com.rx.substation.domain.CalTotalScore;
import com.rx.substation.domain.CalValidateScore;
import com.rx.substation.domain.Inspection;
import com.rx.substation.domain.Transfer;
import com.rx.substation.service.MonthCompleteStatusService;
import com.rx.substation.service.MonthQCInspectionService;
import com.rx.substation.service.MonthTransferService;
import com.rx.substation.service.MonthValidateService;
import com.rx.substation.service.StationService;
import com.rx.substation.util.DateCycleUtil;
import com.rx.substation.util.MathUtil;

/**
 * 定时计算评分任务
 * @author dcx
 *
 */

@RestController
@Component
@RequestMapping(value = "/comment")
public class CommentController {
	
	@Autowired
	MonthTransferService monthTransferService;
	
	@Autowired
	MonthValidateService monthValidateService;
	
	@Autowired
	MonthValidateDAO monthValidateDAO;
	
	@Autowired
	MonthCompleteStatusService monthCompleteStatusService;
	
	@Autowired
	MonthQCInspectionService monthQCInspectionService;
	
	@Autowired
	StationService stationService;
	
	@Autowired
	MonthStationRunningDAO monthStationRunningDAO;
	
	@Autowired
	MonthRunUnitRunningDAO monthRunUnitRunningDAO;
	
	@Autowired
	MonthQCInspectionDAO monthQCInspectionDAO;
	
	@Autowired
	MonthRunUnitQCDAO monthRunUnitQCDAO;
	
	@Autowired
	CalTotalScoreDAO calTotalScoreDAO;
	
	String fileToBeRead = "src/main/resources/Province.xlsx"; // excel位置
	
	//每月15号凌晨15分开始执行
//	@Scheduled(cron="0 15 0 15 * ?")
	@Scheduled(cron="0 15 0 8 * ?")
	@RequestMapping(value="/commentscore")
	//事务管理
	@Transactional(value="txManager")
	public void run() throws Exception{
		
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
		df.setTimeZone(TimeZone.getTimeZone("GMT+8"));
		System.out.println("计算任务开始"+df.format(new Date()));
        
		Calendar cal = Calendar.getInstance();
    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    	sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
    	//获得上个月月初
    	cal.add(Calendar.MONTH, -1);
	    cal.set(Calendar.DATE, 1);
	    String start = sdf.format(cal.getTime());
	    System.out.println(start);
	    //获得上个月月末
	    Calendar cal2 = Calendar.getInstance();
	    cal2.add(Calendar.MONTH, -1);
	    cal2.set(Calendar.DATE,cal2.getActualMaximum(Calendar.DAY_OF_MONTH));
	    String end = sdf.format(cal2.getTime());
	    System.out.println(end);
        //-------------------------------------------进行运算----------------------------------
		//得到所有站点的传输率
    	List<Map<String, Object>> monthTransfer = monthTransferService.getMonthTransfer(start, end);
    	//本月应查询上个月的有效数据表
	    List<String> stationList = stationService.getAllStationCode(start, end);
    	
    	//遍历扣分表
    	List<Map<String, Object>> monthQCInspection = monthQCInspectionService.getMonthQCInspection(start,end);
    	System.out.println("写入excel开始");
		System.out.println(monthTransfer.size());
    	//遍历每个集合写入对应的excel，sheet中
        for (int i = 0; i < monthTransfer.size(); i++) {
//    	for (int i = 0; i < 5; i++) {
    		String stationCode = stationList.get(i);
    		System.out.println(stationCode);
            Map<String, Object> mv = monthValidateService.getMonthValidate(start,end,stationCode);
			
    		Map<String, Object> mt = monthTransfer.get(i);
    		
    		
    		//传输率
    		for (String k : mt.keySet()) {
    			System.out.println(k + " : " + mt.get(k)); 
      	        String key = k;
      	        String value = mt.get(k).toString();
      	        inputTransferExcel("sheet1", 3, key,value);
      	        System.out.println("传输率写入中....");
			}
    		System.out.println(saveMonthTransfer());
    		monthTransferService.save(saveMonthTransfer());
    		
    		//有效天数
    		for (String k : mv.keySet()){ 
    	        System.out.println(k + " : " + mv.get(k)); 
    	        String key = k;
    	        String value = mv.get(k).toString();
    	        
    	        inputValidateExcel("sheet2", 2, key,value);
    	        System.out.println("有效天数写入中....");
    	    } 
    		System.out.println(saveMonthValidate(start,end,stationCode));
    		monthValidateService.save(saveMonthValidate(start,end,stationCode));
    		
            //周报月报完成情况
        	Map<String, Object> monthCompleteStatus = monthCompleteStatusService.getMonthCompleteStatus(start,end,stationCode);
        	for (String k : monthCompleteStatus.keySet()){ 
        		System.out.println(k + " : " + monthCompleteStatus.get(k)); 
        		String key = k;
        		String value = monthCompleteStatus.get(k).toString();
        		inputDayWorkExcel("sheet3", key, value,stationCode);
        		System.out.println("周报完成情况写入中....");
        	}
        	
        	List<Map<String, Object>> stationWork = null;
			try {
				stationWork = getStationWork();
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        	for (int j = 0; j < stationWork.size(); j++) {
        		Map<String, Object> map = stationWork.get(j);
        		monthRunUnitQCDAO.saveStationWork(map);
        	}
	    	
    		monthRunUnitQCDAO.saveRunUnitWork(getRunUnitWork());
	    	
	    	//计算总分
            CalTotalScore calTotalScore = getMonthTotalScore("sheet6");
            //判断该站点是否存在检查扣分
            if(monthQCInspection.size()>0){
            	Double totalDeduct = 0.0;
        		for (int j = 0; j < monthQCInspection.size(); j++) {
        			Map<String,Object> mq = monthQCInspection.get(j);
        			if(calTotalScore.getStationCode().equals(mq.get("stationCode"))){
        				//扣分计算
        				totalDeduct =totalDeduct + Double.valueOf(mq.get("deduct").toString());
        			}
        		}
        		calTotalScore.setQcDeductScore(totalDeduct);
        		Double totalScore = calTotalScore.getTotalScore();
        		totalScore = totalScore-totalDeduct;
        		calTotalScore.setTotalScore(totalScore);
        	}
            
            //运行质量写入
            Transfer transfer = saveMonthTransfer();
            Double stationtransfer = transfer.getAvgTransfer();
            String stationName = transfer.getStationName();
            CalValidateScore validate = saveMonthValidate(start,end,stationCode);
            int validatyDays = validate.getValidatyDays();
            Double totalScore = calTotalScore.getTotalScore();
            inputRunQCExcel("sheet5",stationtransfer,validatyDays,totalScore,stationCode);
       	    //运行单位站点质量评估
            Map<String, Object> saveMonthStationCheck = saveMonthStationCheck();
    	    monthStationRunningDAO.saveMonthStation(saveMonthStationCheck);
    	    
    	    inputRunDayWorkExcel("sheet5",stationCode);
    	    Map<String, Object> saveMonthRunUnitCheck = saveMonthRunUnitCheck();
//    	    String runUnitId = saveMonthRunUnitCheck.get("runUnitId").toString();
//    	    String monthTime = saveMonthRunUnitCheck.get("monthTime").toString();
//    	    monthRunUnitRunningDAO.delete(runUnitId,monthTime);
    	    monthRunUnitRunningDAO.saveMonthRunUnit(saveMonthRunUnitCheck);
    	    
    	    //保存总分表(拿到该站点系数与该站点运行单位系数)
    	    Double a = 0.0;
    	    Double b = 0.0;
    	    for(String k : saveMonthStationCheck.keySet()){
    	    	a = (Double) saveMonthStationCheck.get("coefficient");
    	    }
    	    for(String k : saveMonthRunUnitCheck.keySet()){
    	    	b = (Double) saveMonthRunUnitCheck.get("runQuality");
    	    }
    	    calTotalScore.setTotalCost(a*b*13300);
            calTotalScoreDAO.saveCalTotalScore(calTotalScore);
            System.out.println(calTotalScore);
			}
    	
    	
    	//每个站点费用重新计算
    	for (int i = 0; i < monthTransfer.size(); i++) {
    		Map<String, Object> mtf = monthTransfer.get(i);
    		String stationCode = mtf.get("StationCode").toString();
    		String stationName = mtf.get("StationName").toString();
    	    String runUnitId = monthStationRunningDAO.getOperationCompany(stationCode);
    		
    		Map<String, Object> findRunitQuality = monthRunUnitRunningDAO.findRunitQualityById(start,end,runUnitId);
    		Double runQuality = MathUtil.getBigDecimal(findRunitQuality.get("runQuality")).doubleValue();
    		Double coefficientByStationCode = monthStationRunningDAO.getCoefficientByStationCode(start, end, stationCode);
    		System.out.println(runQuality+":"+coefficientByStationCode);
    		Double stationCost = 0.0;
    		if(findRunitQuality!=null && coefficientByStationCode!=null){
    			stationCost = runQuality*coefficientByStationCode*13300;
    		}else{
    			break;
    		}
    		
    		calTotalScoreDAO.updateCost(start,end,stationCost,stationCode);
		}
            	
        }
	/**
	 * 传输率写入
	 * @param sheetName sheet名称
	 * @param rowNum	行数
	 * @param key	
	 * @param value
	 */
	public void inputTransferExcel(String sheetName,int rowNum,String key,String value) {
		
		FileInputStream fis = null;
		OutputStream out = null;
        try {
        	fis = new FileInputStream(fileToBeRead);
            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            XSSFSheet sheet = workbook.getSheet(sheetName);
            XSSFRow row = sheet.getRow(rowNum);
            //除自动运算单元格，其他单元格需填入设置值
            System.out.println(key+value);
            if(key.equals("StationCode")){
            	row.createCell(0).setCellValue(value);
            } else if(key.equals("StationName")){
            	row.createCell(10).setCellValue(value);
            }else if(key.equals("SO2")){
            	row.createCell(1).setCellValue((String.valueOf(Double.valueOf(value)/100)));
            }else if(key.equals("NO2")){
            	row.createCell(2).setCellValue((String.valueOf(Double.valueOf(value)/100)));
            }else if(key.equals("O3")){
            	row.createCell(3).setCellValue((String.valueOf(Double.valueOf(value)/100)));
            }else if(key.equals("CO")){
            	row.createCell(4).setCellValue((String.valueOf(Double.valueOf(value)/100)));
            }else if(key.equals("PM10")){
            	row.createCell(5).setCellValue((String.valueOf(Double.valueOf(value)/100)));
            }else if(key.equals("PM2_5")){
            	row.createCell(6).setCellValue((String.valueOf(Double.valueOf(value)/100)));
            }
            out =  new FileOutputStream(fileToBeRead);  
            workbook.write(out);  
            System.out.println("写入excel结束");
            
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
        	try {
        		if(out != null && fis != null){
        			out.flush();
					out.close();
					fis.close();
        		}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	        
	}

	/**
	 * 有效天数写入
	 * @param sheetName
	 * @param rowNum
	 * @param key
	 * @param value
	 */
	private void inputValidateExcel(String sheetName,int rowNum,String key,String value) {
		FileInputStream fis = null;
		OutputStream out = null;
        try {
        	 fis = new FileInputStream(fileToBeRead);
        	 XSSFWorkbook workbook = new XSSFWorkbook(fis);
             XSSFSheet sheet = workbook.getSheet(sheetName);
             XSSFRow row = sheet.getRow(rowNum);
             //除自动运算单元格，其他单元格需填入设置值
             //上个月理论天数
             Calendar cal = Calendar.getInstance();
             cal.add(Calendar.MONTH, -1);
             int upDayNum = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
             row.createCell(1).setCellValue(upDayNum);
             //断电天数
             row.createCell(2).setCellValue(0);
             //其他原因
             row.createCell(3).setCellValue(0);
             
             //有效天数
             if(key.equals("validatedays")){
            	 row.createCell(5).setCellValue(value);
             } 
             out =  new FileOutputStream(fileToBeRead);  
             workbook.write(out);  
         } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
        	try {
        		fis.close();
        		if(fis != null){
        			fis.close();
        		}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 周报月报半年报年报写入
	 * 
	 * @param sheetName
	 * @param key
	 * @param value
	 */
	private void inputDayWorkExcel(String sheetName,String key,String value,String stationCode) {
		 FileInputStream fis = null;
		 OutputStream out = null;
	        try {
	        	fis = new FileInputStream(fileToBeRead);
	            XSSFWorkbook workbook = new XSSFWorkbook(fis);
	            XSSFSheet sheet = workbook.getSheet(sheetName);
	            XSSFRow row1 = sheet.getRow(1);
	            XSSFRow row3 = sheet.getRow(3);
	            XSSFRow row4 = sheet.getRow(4);
	            XSSFRow row5 = sheet.getRow(5);
	            XSSFRow row6 = sheet.getRow(6);
	            XSSFRow row7 = sheet.getRow(7);
	            XSSFRow row8 = sheet.getRow(8);
	            XSSFRow row9 = sheet.getRow(9);
	            //写入该站点对应运行单位id
	            String stationName = row1.getCell(5).getStringCellValue();
	            String operationCompany = monthStationRunningDAO.getOperationCompany(stationCode);
	            sheet.getRow(1).createCell(3).setCellValue(operationCompany);
	            //写入城市id
	            sheet.getRow(1).createCell(6).setCellValue(monthStationRunningDAO.getCityId(stationCode));
	            
	            //除自动运算单元格，其他单元格需填入设置值
	           if(key.equals("weektime0")){
	        	    row3.createCell(3).setCellValue(value);
	            }else if(key.equals("weektime1")){
	            	row3.createCell(2).setCellValue(value);
	            	row4.createCell(3).setCellValue(value);
	            }else if(key.equals("weektime2")){
	            	row4.createCell(2).setCellValue(value);
	            	row5.createCell(3).setCellValue(value);
	            }else if(key.equals("weektime3")){
	            	row5.createCell(2).setCellValue(value);
	            	row6.createCell(3).setCellValue(value);
	            }else if(key.equals("weektime4")){
	            	row6.createCell(2).setCellValue(value);
	            }else if(key.equals("monthtime0")){
	            	row7.createCell(3).setCellValue(value);
	            }else if(key.equals("monthtime1")){
	            	row7.createCell(2).setCellValue(value);
	            }else if(key.equals("halfyear0")){
	            	row8.createCell(3).setCellValue(value);
	            }else if(key.equals("halfyear1")){
	            	row8.createCell(2).setCellValue(value);
	            }else if(key.equals("year0")){
	            	row9.createCell(3).setCellValue(value);
	            }else if(key.equals("year1")){
	            	row9.createCell(2).setCellValue(value);
	            }
	           out =  new FileOutputStream(fileToBeRead);  
	           workbook.write(out);  
	           
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }finally {
	        	try {
	        		if(out!=null){
	        			out.flush();
	        			out.close();
	        		}
	        		fis.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
	}
	
	/**
	 * 运行单位运行质量评估写入
	 * @param sheetName
	 * @param stationtransfer
	 * @param validatyDays
	 * @param totalScore
	 * @param stationCode
	 */
	private void inputRunQCExcel(String sheetName,Double stationtransfer,int validatyDays,Double totalScore,String stationCode) {
		 FileInputStream fis = null;
		 OutputStream out = null;
	        try {
	        	//写入站点运行质量信息
	        	 fis = new FileInputStream(fileToBeRead);
	        	 XSSFWorkbook workbook = new XSSFWorkbook(fis);
	             XSSFSheet sheet = workbook.getSheet(sheetName);
	             
	             //写入该站点对应运行单位id
	             String operationCompany = monthStationRunningDAO.getOperationCompany(stationCode);
	             sheet.getRow(1).createCell(5).setCellValue(operationCompany);
	             Calendar calendar = Calendar.getInstance();
	             SimpleDateFormat s = new SimpleDateFormat("yyyy-MM");
	             s.setTimeZone(TimeZone.getTimeZone("GMT+8"));
	         	 //获得上个月月初
	             calendar.add(Calendar.MONTH, -1);
	             calendar.set(Calendar.DATE, 1);
	     	     String checkTime = s.format(calendar.getTime());
	     	     sheet.getRow(1).createCell(4).setCellValue(checkTime);
	             
	             XSSFRow row = sheet.getRow(6);
	             if(stationtransfer < 0.85 || validatyDays <= 27 || totalScore < 80.0){
	            	 row.createCell(1).setCellValue(0.0);
                 }else if(totalScore >= 80 && totalScore < 95.0){
                	 row.createCell(1).setCellValue(totalScore/100);
                 }else{
                	 row.createCell(1).setCellValue(1);
                 }
  
	            out =  new FileOutputStream(fileToBeRead);  
		        workbook.write(out);  
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }finally {
	        	try {
	        		out.flush();
	        		fis.close();
					out.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
	}
	
	
	private void inputRunDayWorkExcel(String sheetName,String stationCode) {
		 FileInputStream fis = null;
		 OutputStream out = null;
	        try {
	        	//写入站点运行质量信息
	        	 fis = new FileInputStream(fileToBeRead);
	        	 XSSFWorkbook workbook = new XSSFWorkbook(fis);
	             XSSFSheet sheet = workbook.getSheet(sheetName);
	             
	             //写入该站点对应运行单位id
	             String operationCompany = monthStationRunningDAO.getOperationCompany(stationCode);
	             sheet.getRow(1).createCell(5).setCellValue(operationCompany);
	             //写入该站点对应运行单位的质量评估信息
	             Calendar cal = Calendar.getInstance();
	             SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
	             sdf1.setTimeZone(TimeZone.getTimeZone("GMT+8"));
	         	 //获得上个月月初
	         	 cal.add(Calendar.MONTH, -1);
	     	     cal.set(Calendar.DATE, 1);
	     	     String start = sdf1.format(cal.getTime());
	     	     //获得上个月月末
	     	     Calendar cal2 = Calendar.getInstance();
	     	     cal2.add(Calendar.MONTH, -1);
	     	     cal2.set(Calendar.DATE,cal2.getActualMaximum(Calendar.DAY_OF_MONTH));
	     	     String end = sdf1.format(cal2.getTime());
	     	     //获得上上个月月初
	     	     Calendar cal3 = Calendar.getInstance();
	     	     cal3.add(Calendar.MONTH, -2);
	     	     cal3.set(Calendar.DATE, 1);
	     	     String start1 = sdf1.format(cal3.getTime());
	     	     //获得上上个月月末
	     	     Calendar cal4 = Calendar.getInstance();
	     	     cal4.add(Calendar.MONTH, -2);
	     	     cal4.set(Calendar.DATE,cal4.getActualMaximum(Calendar.DAY_OF_MONTH));
	     	     String end1 = sdf1.format(cal4.getTime());
	     	     //获得前三个月月初
	     	     Calendar cal5 = Calendar.getInstance();
	     	     cal5.add(Calendar.MONTH, -3);
	     	     cal5.set(Calendar.DATE, 1);
	     	     String start2 = sdf1.format(cal5.getTime());
	     	     //获得前三个月月末
	     	     Calendar cal6 = Calendar.getInstance();
	     	     cal6.add(Calendar.MONTH, -3);
	     	     cal6.set(Calendar.DATE,cal6.getActualMaximum(Calendar.DAY_OF_MONTH));
	     	     String end2 = sdf1.format(cal6.getTime());
	     	     //获得前四个月月初
	     	     Calendar cal7 = Calendar.getInstance();
	     	     cal7.add(Calendar.MONTH, -4);
	     	     cal7.set(Calendar.DATE, 1);
	     	     String start3 = sdf1.format(cal7.getTime());
	     	     //获得前四个月月末
	     	     Calendar cal8 = Calendar.getInstance();
	     	     cal8.add(Calendar.MONTH, -4);
	     	     cal8.set(Calendar.DATE,cal8.getActualMaximum(Calendar.DAY_OF_MONTH));
	     	     String end3 = sdf1.format(cal8.getTime());
	     	     //有效天数判断（日累计27（25），6参是否存在超过8天无效数据）
	             int j = 0;
	             List<String> stationcodelist = monthStationRunningDAO.getOperationCompanyStations(stationCode);
	             for (int i = 0; i < stationcodelist.size(); i++) {
	            	 String stationCode1 = stationcodelist.get(i);
//	            	 Double coefficientByStationCode = monthStationRunningDAO.getCoefficientByStationCode(start,end,stationCode);
//	            	 if(coefficientByStationCode !=null && coefficientByStationCode == 0.0){
//	            		 j++;
//	            	 }
	            	 if(monthValidateDAO.getMonthValidate(start,end,stationCode1) != null){
		     	    		Map<String,Object> monthValidate = monthValidateDAO.getMonthValidate(start,end,stationCode1);
		     	    		String TimePoint = monthValidate.get("TimePoint").toString();
		     	    		int SO2 = Integer.valueOf(monthValidate.get("SO2").toString());
		     	    		int NO2 = Integer.valueOf(monthValidate.get("NO2").toString());
		     	    		int CO = Integer.valueOf(monthValidate.get("CO").toString());
		     	    		int PM10 = Integer.valueOf(monthValidate.get("PM10").toString());
		     	    		int PM2_5 = Integer.valueOf(monthValidate.get("PM2_5").toString());
		     	    		int O3 = Integer.valueOf(monthValidate.get("O3").toString());
		     	    		int Total = Integer.valueOf(monthValidate.get("Total").toString());
		     	    		
		     	    		
		     	    		cal.add(Calendar.MONTH, -1);
		     	    		int upDayNum = cal.getActualMaximum(Calendar.DAY_OF_MONTH)-8;
		     	    		try {
		     	    			if(sdf1.parse(TimePoint).getMonth()==2){
		     	    				if(!(SO2 > upDayNum && NO2 > upDayNum && CO > upDayNum && PM10 > upDayNum && PM2_5 > upDayNum && O3 > upDayNum && Total > 25)){
		     	    					j++;
		     	    				} 
		     	    			}else{
		     	    				if(!(SO2 > upDayNum && NO2 > upDayNum && CO > upDayNum && PM10 > upDayNum && PM2_5 > upDayNum && O3 > upDayNum && Total > 27)){
		     	    					j++;
		     	    				}
		     	    			}
		     	    		} catch (ParseException e) {
		     	    			// TODO Auto-generated catch block
		     	    			e.printStackTrace();
		     	    		}
		     	    		
		     	    	}
	            	 
				 }
	             //当月20%站点未达到数据有效性要求
	             if(stationcodelist.size() > 0 && j/stationcodelist.size() > 0.2){
	            	 sheet.getRow(9).createCell(2).setCellValue(1);
	             }else{
	            	 sheet.getRow(9).createCell(2).setCellValue(0);
	             }
	             //当月40%站点未达到数据有效性要求
	             if(stationcodelist.size() > 0 && j/stationcodelist.size() > 0.4){
	            	 sheet.getRow(11).createCell(2).setCellValue(1);
	             }else{
	            	 sheet.getRow(11).createCell(2).setCellValue(0);
	             }
	             
	             int j2 = 0;
	             for (int i = 0; i < stationcodelist.size(); i++) {
	            	 String stationCode1 = stationcodelist.get(i);
//	            	 Double coefficientByStationCode = monthStationRunningDAO.getCoefficientByStationCode(start,end,stationCode);
//	            	 if(coefficientByStationCode !=null && coefficientByStationCode == 0.0){
//	            		 j2++;
//	            	 }
	            	 if(monthValidateDAO.getMonthValidate(start,end,stationCode1) != null){
		     	    		Map<String,Object> monthValidate = monthValidateDAO.getMonthValidate(start,end,stationCode1);
		     	    		String TimePoint = monthValidate.get("TimePoint").toString();
		     	    		int SO2 = Integer.valueOf(monthValidate.get("SO2").toString());
		     	    		int NO2 = Integer.valueOf(monthValidate.get("NO2").toString());
		     	    		int CO = Integer.valueOf(monthValidate.get("CO").toString());
		     	    		int PM10 = Integer.valueOf(monthValidate.get("PM10").toString());
		     	    		int PM2_5 = Integer.valueOf(monthValidate.get("PM2_5").toString());
		     	    		int O3 = Integer.valueOf(monthValidate.get("O3").toString());
		     	    		int Total = Integer.valueOf(monthValidate.get("Total").toString());
		     	    		
		     	    		
		     	    		cal.add(Calendar.MONTH, -1);
		     	    		int upDayNum = cal.getActualMaximum(Calendar.DAY_OF_MONTH)-8;
		     	    		try {
		     	    			if(sdf1.parse(TimePoint).getMonth()==2){
		     	    				if(!(SO2 > upDayNum && NO2 > upDayNum && CO > upDayNum && PM10 > upDayNum && PM2_5 > upDayNum && O3 > upDayNum && Total > 25)){
		     	    					j2++;
		     	    				} 
		     	    			}else{
		     	    				if(!(SO2 > upDayNum && NO2 > upDayNum && CO > upDayNum && PM10 > upDayNum && PM2_5 > upDayNum && O3 > upDayNum && Total > 27)){
		     	    					j2++;
		     	    				}
		     	    			}
		     	    		} catch (ParseException e) {
		     	    			// TODO Auto-generated catch block
		     	    			e.printStackTrace();
		     	    		}
		     	    		
		     	    	}
	            	 
				 }
	             int j3 = 0;
	             for (int i = 0; i < stationcodelist.size(); i++) {
	            	 String stationCode1 = stationcodelist.get(i);
//	            	 Double coefficientByStationCode = monthStationRunningDAO.getCoefficientByStationCode(start1,end1,stationCode);
//	            	 if(coefficientByStationCode !=null && coefficientByStationCode == 0.0){
//	            		 j3++;
//	            	 }
	            	 if(monthValidateDAO.getMonthValidate(start1,end1,stationCode1) != null){
		     	    		Map<String,Object> monthValidate = monthValidateDAO.getMonthValidate(start1,end1,stationCode1);
		     	    		String TimePoint = monthValidate.get("TimePoint").toString();
		     	    		int SO2 = Integer.valueOf(monthValidate.get("SO2").toString());
		     	    		int NO2 = Integer.valueOf(monthValidate.get("NO2").toString());
		     	    		int CO = Integer.valueOf(monthValidate.get("CO").toString());
		     	    		int PM10 = Integer.valueOf(monthValidate.get("PM10").toString());
		     	    		int PM2_5 = Integer.valueOf(monthValidate.get("PM2_5").toString());
		     	    		int O3 = Integer.valueOf(monthValidate.get("O3").toString());
		     	    		int Total = Integer.valueOf(monthValidate.get("Total").toString());
		     	    		
		     	    		
		     	    		cal.add(Calendar.MONTH, -1);
		     	    		int upDayNum = cal.getActualMaximum(Calendar.DAY_OF_MONTH)-8;
		     	    		try {
		     	    			if(sdf1.parse(TimePoint).getMonth()==2){
		     	    				if(!(SO2 > upDayNum && NO2 > upDayNum && CO > upDayNum && PM10 > upDayNum && PM2_5 > upDayNum && O3 > upDayNum && Total > 25)){
		     	    					j3++;
		     	    				} 
		     	    			}else{
		     	    				if(!(SO2 > upDayNum && NO2 > upDayNum && CO > upDayNum && PM10 > upDayNum && PM2_5 > upDayNum && O3 > upDayNum && Total > 27)){
		     	    					j3++;
		     	    				}
		     	    			}
		     	    		} catch (ParseException e) {
		     	    			// TODO Auto-generated catch block
		     	    			e.printStackTrace();
		     	    		}
		     	    	//特殊，如果上个月无数据及算为有效（针对刚开始的评估）
		     	    	}else{
						 	j3++;
					 }
	            	 
				 }
	             //连续两个月20%未达到数据有效要求
	             if(stationcodelist.size() > 0 && j2/stationcodelist.size()>0.2 && j3/stationcodelist.size()>0.2){
	            	 sheet.getRow(10).createCell(2).setCellValue(1);
	             }else{
	            	 sheet.getRow(10).createCell(2).setCellValue(0);
	             }
	             
	             //同一站点连续两个月未达到数据有效性
	             for (int i = 0; i < stationcodelist.size(); i++) {
	            	 String stationCode1 = stationcodelist.get(i);
	            	 Double coefficientByStationCode = monthStationRunningDAO.getCoefficientByStationCode(start,end,stationCode1);
	            	 Double coefficientByStationCode2 = monthStationRunningDAO.getCoefficientByStationCode(start1,end1,stationCode1);
	            	 if(coefficientByStationCode !=null && coefficientByStationCode == 0.0 
	            			 && coefficientByStationCode2 !=null && coefficientByStationCode2 == 0.0  ){
	            		 sheet.getRow(12).createCell(2).setCellValue(1);
	            		 break;
		             }else{
		            	 sheet.getRow(12).createCell(2).setCellValue(0);
		             }
				 }
	             
	             //同一站点连续三个月未达到数据有效性
	             for (int i = 0; i < stationcodelist.size(); i++) {
	            	 String stationCode1 = stationcodelist.get(i);
	            	 Double coefficientByStationCode = monthStationRunningDAO.getCoefficientByStationCode(start,end,stationCode1);
	            	 Double coefficientByStationCode2 = monthStationRunningDAO.getCoefficientByStationCode(start1,end1,stationCode1);
	            	 Double coefficientByStationCode3 = monthStationRunningDAO.getCoefficientByStationCode(start2,end2,stationCode1);
	            	 if(coefficientByStationCode != null && coefficientByStationCode == 0.0 &&
	            			 coefficientByStationCode2 != null && coefficientByStationCode2 == 0.0 &&
	            					 coefficientByStationCode3 != null && coefficientByStationCode3 == 0.0){
	            		 sheet.getRow(13).createCell(2).setCellValue(1);
	            		 break;
		             }else{
		            	 sheet.getRow(13).createCell(2).setCellValue(0);
		             }
				 }
	             //同一站点连续四个月未达到数据有效性
	             for (int i = 0; i < stationcodelist.size(); i++) {
	            	 String stationCode1 = stationcodelist.get(i);
	            	 Double coefficientByStationCode = monthStationRunningDAO.getCoefficientByStationCode(start,end,stationCode1);
	            	 Double coefficientByStationCode2 = monthStationRunningDAO.getCoefficientByStationCode(start1,end1,stationCode1);
	            	 Double coefficientByStationCode3 = monthStationRunningDAO.getCoefficientByStationCode(start2,end2,stationCode1);
	            	 Double coefficientByStationCode4 = monthStationRunningDAO.getCoefficientByStationCode(start3,end3,stationCode1);
	            	 
	            	 if(coefficientByStationCode != null && coefficientByStationCode == 0.0 &&
	            			 coefficientByStationCode2 != null && coefficientByStationCode2 == 0.0 &&
        					 coefficientByStationCode3 != null && coefficientByStationCode3 == 0.0 &&
        					 coefficientByStationCode4 != null && coefficientByStationCode4 == 0.0){
	            		 sheet.getRow(14).createCell(2).setCellValue(1);
	            		 break;
		             }else{
		            	 sheet.getRow(14).createCell(2).setCellValue(0);
		             }
				 }
//	            sheet.getRow(14).createCell(2).setCellValue(false);  
//	            sheet.getRow(13).createCell(2).setCellValue(false);
//	            sheet.getRow(12).createCell(2).setCellValue(false);
//	            sheet.getRow(10).createCell(2).setCellValue(false);
	            
	            out =  new FileOutputStream(fileToBeRead);  
		        workbook.write(out);  
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }finally {
	        	try {
	        		fis.close();
	        		if(out!=null){
	        			out.flush();
	        			out.close();
	        		}
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
	}
	
	
	private CalTotalScore getMonthTotalScore(String sheetName) {
		CalTotalScore calTotalScore = new CalTotalScore();
        try {
        	FileInputStream fis = new FileInputStream(fileToBeRead);
        	 XSSFWorkbook workbook = new XSSFWorkbook(fis);
             XSSFSheet sheet = workbook.getSheet(sheetName);
             XSSFRow row = sheet.getRow(1);
             XSSFFormulaEvaluator eval=new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
     		 eval.evaluateFormulaCell(row.getCell(0));  
     		 eval.evaluateFormulaCell(row.getCell(1));  
     		 eval.evaluateFormulaCell(row.getCell(2));  
     		
     		 eval.evaluateFormulaCell(row.getCell(5));
     		 eval.evaluateFormulaCell(row.getCell(6));
             row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
            calTotalScore.setStationCode(row.getCell(0).getStringCellValue());
            row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
            calTotalScore.setStationName(row.getCell(6).getStringCellValue());
            
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
            sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
        	 //获得上个月月初
            Calendar cal = Calendar.getInstance();
        	 cal.add(Calendar.MONTH, -1);
    	     String monthTime = sdf.format(cal.getTime());
    	    try {
    	    	calTotalScore.setMonthTime(sdf.parse(monthTime));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
            
            calTotalScore.setTansferScore(row.getCell(1).getNumericCellValue());
            calTotalScore.setValidateDaysScore(row.getCell(2).getNumericCellValue());
            eval.evaluateFormulaCell(row.getCell(3)); 
            calTotalScore.setCompleteScore(row.getCell(3).getNumericCellValue());
            
            
            calTotalScore.setQcDeductScore(0.0);
            calTotalScore.setTotalScore(row.getCell(5).getNumericCellValue());
            //计算上月总钱开销（运行费）
            Calendar cal2 = Calendar.getInstance();
            SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
            sdf1.setTimeZone(TimeZone.getTimeZone("GMT+8"));
        	//获得上个月月初
        	cal2.add(Calendar.MONTH, -1);
    	    cal2.set(Calendar.DATE, 1);
    	    String start = sdf1.format(cal2.getTime());
    	    //获得上个月月末
    	    Calendar cal3 = Calendar.getInstance();
    	    cal3.add(Calendar.MONTH, -1);
    	    cal3.set(Calendar.DATE,cal2.getActualMaximum(Calendar.DAY_OF_MONTH));
    	    String end = sdf1.format(cal3.getTime());
    	    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
//    	    String stationCode = row.getCell(0).getStringCellValue();
//          Double c = monthStationRunningDAO.getCoefficientByStationCode(start,end,stationCode);
//          System.out.println(c);
            calTotalScore.setTotalCost(13300*1.0);

            fis.close();
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
			return calTotalScore;
	}
	
	/**
	 * 得到传输率
	 * @return
	 */
	private Transfer saveMonthTransfer() {
		Transfer transfer = new Transfer();
        try {
        	 FileInputStream fis = new FileInputStream(fileToBeRead);
        	 XSSFWorkbook workbook = new XSSFWorkbook(fis);
             XSSFSheet sheet = workbook.getSheet("sheet1");
             XSSFRow row = sheet.getRow(3);
            
             XSSFFormulaEvaluator eval=new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
     		 eval.evaluateFormulaCell(row.getCell(7));  
     		 eval.evaluateFormulaCell(row.getCell(8));  
     		 eval.evaluateFormulaCell(row.getCell(9));
             
             transfer.setStationCode(row.getCell(0).getStringCellValue());
             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
             sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
         	 //获得上个月月初
             Calendar cal = Calendar.getInstance();
         	 cal.add(Calendar.MONTH, -1);
     	     String monthTime = sdf.format(cal.getTime());
             
             try {
				transfer.setMonthTime(sdf.parse(monthTime));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
             transfer.setSo2(Double.valueOf(row.getCell(1).getStringCellValue()));
             transfer.setNo2(Double.valueOf(row.getCell(2).getStringCellValue()));
             transfer.setO3(Double.valueOf(row.getCell(3).getStringCellValue()));
             transfer.setCo(Double.valueOf(row.getCell(4).getStringCellValue()));
             transfer.setPm10(Double.valueOf(row.getCell(5).getStringCellValue()));
             transfer.setPm2_5(Double.valueOf(row.getCell(6).getStringCellValue()));
             transfer.setAvgTransfer(row.getCell(7).getNumericCellValue());
             String transferStatus = row.getCell(8).getStringCellValue();
             if("是".equals(transferStatus)){
            	 transfer.setStatus(true);
            	 transfer.setScores(35*((row.getCell(7).getNumericCellValue())-0.8)/0.2);
             }else{
            	 transfer.setStatus(false);
            	 transfer.setScores(0.0);
             }
             transfer.setStationName(row.getCell(10).getStringCellValue());
             
            fis.close();
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
			return transfer;
	}
	
	
	/**
	 * 得到有效天数数据
	 * @return
	 */
	private CalValidateScore saveMonthValidate(String start,String end,String stationCode) {
		CalValidateScore calValidateScore = new CalValidateScore();
        try {
        	 FileInputStream fis = new FileInputStream(fileToBeRead);
        	 XSSFWorkbook workbook = new XSSFWorkbook(fis);
             XSSFSheet sheet = workbook.getSheet("sheet2");
             XSSFRow row = sheet.getRow(2);
             
             XSSFFormulaEvaluator eval=new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
     		 eval.evaluateFormulaCell(row.getCell(0));  
     		 eval.evaluateFormulaCell(row.getCell(4));  
     		 eval.evaluateFormulaCell(row.getCell(6));  
     		 eval.evaluateFormulaCell(row.getCell(7)); 
     		 eval.evaluateFormulaCell(row.getCell(8));
            
             if(row.getCell(0)!=null){
            	 row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
            	 calValidateScore.setStationCode(row.getCell(0).getStringCellValue());
             }
             
             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
             sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
         	 //获得上个月月初
             Calendar cal = Calendar.getInstance();
         	 cal.add(Calendar.MONTH, -1);
     	     String monthTime = sdf.format(cal.getTime());
     	    try {
				calValidateScore.setMonthTime(sdf.parse(monthTime));
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
     	    if(row.getCell(7)!=null){
     	    	row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
     	    	calValidateScore.setStationName(row.getCell(7).getStringCellValue());
     	    }
     	     
             calValidateScore.setTheoryDays((int)row.getCell(1).getNumericCellValue());
             calValidateScore.setPowerCutDays((int)row.getCell(2).getNumericCellValue());
             calValidateScore.setOthers((int)row.getCell(3).getNumericCellValue());
             calValidateScore.setShouldDays((int)row.getCell(4).getNumericCellValue());
             row.getCell(8).setCellType(Cell.CELL_TYPE_NUMERIC);
             calValidateScore.setValidatyDays((int)row.getCell(8).getNumericCellValue());
             
             //有效天数判断（日累计27（25），6参是否存在超过8天无效数据）
             if(monthValidateDAO.getMonthValidate(start,end,stationCode) != null){
    			 Map<String,Object> monthValidate = monthValidateDAO.getMonthValidate(start,end,stationCode);
    			 String TimePoint = monthValidate.get("TimePoint").toString();
    			 int SO2 = Integer.valueOf(monthValidate.get("SO2").toString());
    			 int NO2 = Integer.valueOf(monthValidate.get("NO2").toString());
    			 int CO = Integer.valueOf(monthValidate.get("CO").toString());
    			 int PM10 = Integer.valueOf(monthValidate.get("PM10").toString());
    			 int PM2_5 = Integer.valueOf(monthValidate.get("PM2_5").toString());
    			 int O3 = Integer.valueOf(monthValidate.get("O3").toString());
    			 int Total = Integer.valueOf(monthValidate.get("Total").toString());
    			 
    			 
    			 cal.add(Calendar.MONTH, -1);
                 int upDayNum = cal.getActualMaximum(Calendar.DAY_OF_MONTH)-8;
    			 try {
    				monthValidate.put("datamonth",sdf.parse(TimePoint));
    				if(sdf.parse(TimePoint).getMonth()==2){
    					if(SO2 > upDayNum && NO2 > upDayNum && CO > upDayNum && PM10 > upDayNum && PM2_5 > upDayNum && O3 > upDayNum && Total > 25){
    						calValidateScore.setScores(row.getCell(6).getNumericCellValue());
    					}else{
    						calValidateScore.setScores(0.0);
    					} 
    				 }else{
    					if(SO2 > upDayNum && NO2 > upDayNum && CO > upDayNum && PM10 > upDayNum && PM2_5 > upDayNum && O3 > upDayNum && Total > 27){
    						calValidateScore.setScores(row.getCell(6).getNumericCellValue());
    					}else{
    						calValidateScore.setScores(0.0);
    					}  
    				 }
    			} catch (ParseException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    			
    		}
             
             
            fis.close();
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
			return calValidateScore;
	}
	
	/**
	 * 得到运行单位日常工作评价
	 * @return
	 */
	private Map<String,Object> getRunUnitWork() {
		
		Map<String,Object> stationWork =  new HashMap<>();
        try {
        	 FileInputStream fis = new FileInputStream(fileToBeRead);
        	 XSSFWorkbook workbook = new XSSFWorkbook(fis);
             XSSFSheet sheet = workbook.getSheet("sheet3");
             XSSFRow row1 = sheet.getRow(1);
             XSSFRow row10 = sheet.getRow(10);
             sheet.setForceFormulaRecalculation(true);
             XSSFFormulaEvaluator eval=new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
     		 eval.evaluateFormulaCell(row1.getCell(1));  
     		 eval.evaluateFormulaCell(row1.getCell(5)); 
     		 eval.evaluateFormulaCell(row10.getCell(1));
            
             stationWork.put("stationCode",row1.getCell(1).getStringCellValue());
             stationWork.put("stationName",row1.getCell(5).getStringCellValue());
             stationWork.put("runUnitId",row1.getCell(3).getStringCellValue());
             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
             sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
             Calendar cal = Calendar.getInstance();
             cal.add(Calendar.MONTH, -1);
     	     cal.set(Calendar.DATE, 1);
     	     String checkTime = sdf.format(cal.getTime());
             stationWork.put("checkTime", checkTime);
             stationWork.put("cityId", row1.getCell(6).getStringCellValue());
             stationWork.put("manager", row1.getCell(8).getStringCellValue());
             
             eval.evaluateFormulaCell(row10.getCell(1));
             stationWork.put("deduct", row10.getCell(1).getNumericCellValue());
            //除自动运算单元格，其他单元格需填入设置值
            fis.close();
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
			return stationWork;
	}
	
	private List<Map<String,Object>> getStationWork() throws ParseException {
		
		List<Map<String,Object>> stationWorklist = new ArrayList<>();
		Map<String,Object> stationWork1 =  new HashMap<>();
		Map<String,Object> stationWork2 =  new HashMap<>();
		Map<String,Object> stationWork3 =  new HashMap<>();
		Map<String,Object> stationWork4 =  new HashMap<>();
		Map<String,Object> stationWork5 =  new HashMap<>();
		Map<String,Object> stationWork6 =  new HashMap<>();
		Map<String,Object> stationWork7 =  new HashMap<>();
		
        try {
        	 FileInputStream fis = new FileInputStream(fileToBeRead);
        	 XSSFWorkbook workbook = new XSSFWorkbook(fis);
             XSSFSheet sheet = workbook.getSheet("sheet3");
             XSSFRow row1 = sheet.getRow(1);
             XSSFRow row3 = sheet.getRow(3);
             XSSFRow row4 = sheet.getRow(4);
             XSSFRow row5 = sheet.getRow(5);
             XSSFRow row6 = sheet.getRow(6);
             XSSFRow row7 = sheet.getRow(7);
             XSSFRow row8 = sheet.getRow(8);
             XSSFRow row9 = sheet.getRow(9);
             
             XSSFFormulaEvaluator eval=new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
     		 eval.evaluateFormulaCell(row1.getCell(1));
     		
             stationWork1.put("lastTime",row3.getCell(2).getStringCellValue());
             stationWork1.put("thisTime",row3.getCell(3).getStringCellValue());
             stationWork1.put("type", 1);
             stationWork1.put("theoryCycle", 7);
             
             String stringCellValue = row3.getCell(2).getStringCellValue();
             String stringCellValue2 = row3.getCell(3).getStringCellValue();
             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
             Date lastTime = sdf.parse(stringCellValue);
             Date thisTime = sdf.parse(stringCellValue2);
             Calendar wCalendar1 = Calendar.getInstance();
             Calendar wCalendar2 = Calendar.getInstance();
             wCalendar1.setTime(lastTime);
             wCalendar2.setTime(thisTime);
             
             int inputCycle = DateCycleUtil.getDaysBetween(wCalendar1,wCalendar2);
			 if(inputCycle > 1000 ){
				 inputCycle = 0;
			 }
             Double performance = (inputCycle-7)*0.5;
             
             stationWork1.put("inputCycle", inputCycle);
             stationWork1.put("performance", performance);
             stationWork1.put("mark",row3.getCell(7).getStringCellValue());
//             row1.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
             stationWork1.put("stationCode",row1.getCell(1).getStringCellValue());
             row3.createCell(8).setCellValue(performance);
            
             
             stationWork2.put("lastTime",row4.getCell(2).getStringCellValue());
             stationWork2.put("thisTime",row4.getCell(3).getStringCellValue());
             stationWork2.put("type", 1);
             stationWork2.put("theoryCycle", 7);
             String stringCellValue3 = row4.getCell(2).getStringCellValue();
             String stringCellValue4 = row4.getCell(3).getStringCellValue();
             Date lastTime3 = sdf.parse(stringCellValue3);
             Date thisTime4 = sdf.parse(stringCellValue4);
             Calendar wCalendar3 = Calendar.getInstance();
             Calendar wCalendar4 = Calendar.getInstance();
             wCalendar3.setTime(lastTime3);
             wCalendar4.setTime(thisTime4);
             
             int inputCycle2 = DateCycleUtil.getDaysBetween(wCalendar3,wCalendar4);
			 if(inputCycle2 > 1000 ){
				 inputCycle2 = 0;
			 }
             Double performance2 = (inputCycle2-7)*0.5;
             stationWork2.put("inputCycle", inputCycle2);
             stationWork2.put("performance", performance2);
             stationWork2.put("mark",row4.getCell(7).getStringCellValue());
             stationWork2.put("stationCode",row1.getCell(1).getStringCellValue());
             row4.createCell(8).setCellValue(performance2);
             
             
             
             stationWork3.put("lastTime",row5.getCell(2).getStringCellValue());
             stationWork3.put("thisTime",row5.getCell(3).getStringCellValue());
             stationWork3.put("type", 1);
             stationWork3.put("theoryCycle", 7);
             String stringCellValue5 = row5.getCell(2).getStringCellValue();
             String stringCellValue6 = row5.getCell(3).getStringCellValue();
             Date lastTime5 = sdf.parse(stringCellValue5);
             Date thisTime6 = sdf.parse(stringCellValue6);
             Calendar wCalendar5 = Calendar.getInstance();
             Calendar wCalendar6 = Calendar.getInstance();
             wCalendar5.setTime(lastTime5);
             wCalendar6.setTime(thisTime6);


             int inputCycle3 = DateCycleUtil.getDaysBetween(wCalendar5,wCalendar6);
			 if(inputCycle3 > 1000 ){
			 	inputCycle3 = 0;
			 }
			 Double performance3 = (inputCycle3-7)*0.5;
             stationWork3.put("inputCycle", inputCycle3);
             stationWork3.put("performance", performance3);
             stationWork3.put("mark",row5.getCell(7).getStringCellValue());
             stationWork3.put("stationCode",row1.getCell(1).getStringCellValue());
             row5.createCell(8).setCellValue(performance3);
            
             
             stationWork4.put("lastTime",row6.getCell(2).getStringCellValue());
             stationWork4.put("thisTime",row6.getCell(3).getStringCellValue());
             stationWork4.put("type", 1);
             stationWork4.put("theoryCycle", 7);
             String stringCellValue7 = row6.getCell(2).getStringCellValue();
             String stringCellValue8 = row6.getCell(3).getStringCellValue();
             Date lastTime7 = sdf.parse(stringCellValue7);
             Date thisTime8 = sdf.parse(stringCellValue8);
             Calendar wCalendar7 = Calendar.getInstance();
             Calendar wCalendar8 = Calendar.getInstance();
             wCalendar7.setTime(lastTime7);
             wCalendar8.setTime(thisTime8);
             
             int inputCycle4 = DateCycleUtil.getDaysBetween(wCalendar7,wCalendar8);
			 if(inputCycle4 > 1000 ){
					 inputCycle4 = 0;
				 }
             Double performance4 = (inputCycle4-7)*0.5;
             stationWork4.put("inputCycle", inputCycle4);
             stationWork4.put("performance", performance4);
             stationWork4.put("mark",row6.getCell(7).getStringCellValue());
             stationWork4.put("stationCode",row1.getCell(1).getStringCellValue());
             row6.createCell(8).setCellValue(performance4);
             
             
             stationWork5.put("lastTime",row7.getCell(2).getStringCellValue());
             stationWork5.put("thisTime",row7.getCell(3).getStringCellValue());
             stationWork5.put("type", 2);
             stationWork5.put("theoryCycle", 31);
             String stringCellValue9 = row7.getCell(2).getStringCellValue();
             String stringCellValue10 = row7.getCell(3).getStringCellValue();
             Date lastTime9 = sdf.parse(stringCellValue9);
             Date thisTime10 = sdf.parse(stringCellValue10);
             Calendar mCalendar1 = Calendar.getInstance();
             Calendar mCalendar2 = Calendar.getInstance();
             mCalendar1.setTime(lastTime9);
             mCalendar2.setTime(thisTime10);
             
             int inputCycle5 = DateCycleUtil.getDaysBetween(mCalendar1,mCalendar2);
             if(inputCycle5 > 1000 ){
             	inputCycle5 = 0;
			 }
             Double performance5 = (inputCycle5-31)*1.0;
             stationWork5.put("inputCycle",inputCycle5);
             stationWork5.put("performance", performance5);
             stationWork5.put("mark",row7.getCell(7).getStringCellValue());
             stationWork5.put("stationCode",row1.getCell(1).getStringCellValue());
             row7.createCell(8).setCellValue(performance5);
             
             
             stationWork6.put("lastTime",row8.getCell(2).getStringCellValue());
             stationWork6.put("thisTime",row8.getCell(3).getStringCellValue());
             stationWork6.put("type", 3);
             stationWork6.put("theoryCycle", 183);
             String stringCellValue11 = row8.getCell(2).getStringCellValue();
             String stringCellValue12 = row8.getCell(3).getStringCellValue();
             Date lastTime11 = sdf.parse(stringCellValue11);
             Date thisTime12 = sdf.parse(stringCellValue12);
             Calendar hyCalendar1 = Calendar.getInstance();
             Calendar hyCalendar2 = Calendar.getInstance();
             hyCalendar1.setTime(lastTime11);
             hyCalendar2.setTime(thisTime12);
             
             int inputCycle6 = DateCycleUtil.getDaysBetween(hyCalendar1,hyCalendar2);
			 if(inputCycle6 > 1000 ){
			 	inputCycle6 = 0;
			 }
             Double performance6 = (inputCycle6-183)*1.0;
             stationWork6.put("inputCycle", inputCycle6);
             stationWork6.put("performance", performance6);
             stationWork6.put("mark",row8.getCell(7).getStringCellValue());
             stationWork6.put("stationCode",row1.getCell(1).getStringCellValue());
             row8.createCell(8).setCellValue(performance6);
             
             
             
             stationWork7.put("lastTime",row9.getCell(2).getStringCellValue());
             stationWork7.put("thisTime",row9.getCell(3).getStringCellValue());
             stationWork7.put("type", 4);
             stationWork7.put("theoryCycle", 365);
             String stringCellValue13 = row9.getCell(2).getStringCellValue();
             String stringCellValue14 = row9.getCell(3).getStringCellValue();
             Date lastTime13 = sdf.parse(stringCellValue13);
             Date thisTime14 = sdf.parse(stringCellValue14);
             
             
             Calendar yCalendar1 = Calendar.getInstance();
             Calendar yCalendar2 = Calendar.getInstance();
             yCalendar1.setTime(lastTime13);
             yCalendar2.setTime(thisTime14);
             
             int inputCycle7 = DateCycleUtil.getDaysBetween(yCalendar1,yCalendar2);
			 if(inputCycle7 > 1000 ){
				inputCycle7 = 0;
			 }
             Double performance7 = (inputCycle7-365)*1.0;
             stationWork7.put("inputCycle", inputCycle7);
             stationWork7.put("performance", performance7);
             stationWork7.put("mark",row9.getCell(7).getStringCellValue());
             stationWork7.put("stationCode",row1.getCell(1).getStringCellValue());
             row9.createCell(8).setCellValue(performance7);
             
             stationWorklist.add(stationWork1);
             stationWorklist.add(stationWork2);
             stationWorklist.add(stationWork3);
             stationWorklist.add(stationWork4);
             stationWorklist.add(stationWork5);
             stationWorklist.add(stationWork6);
             stationWorklist.add(stationWork7);
            
            FileOutputStream out = new FileOutputStream(fileToBeRead);
            workbook.write(out);
            
            fis.close();
            out.close();
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
			return stationWorklist;
	}
	
	/**
	 * 得到运行单位运行质量
	 * @return
	 */
	private Map<String,Object> saveMonthRunUnitCheck() {
		
		Map<String,Object> runUnitCheck =  new HashMap<>();
		
        try {
        	FileInputStream fis = new FileInputStream(fileToBeRead);
        	 XSSFWorkbook workbook = new XSSFWorkbook(fis);
             XSSFSheet sheet = workbook.getSheet("sheet5");
             XSSFRow row = sheet.getRow(1);
             
             XSSFFormulaEvaluator eval=new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
     		 eval.evaluateFormulaCell(row.getCell(1));  
     		 eval.evaluateFormulaCell(row.getCell(2));
             
             
             runUnitCheck.put("runUnitId", row.getCell(5).getStringCellValue());
             
             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
             sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
             Date monthTime;
			try {
				monthTime = sdf.parse(row.getCell(4).getStringCellValue());
				runUnitCheck.put("monthTime", monthTime);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			
			
			int status1 = (int)sheet.getRow(9).getCell(2).getNumericCellValue();
			int status2 = (int)sheet.getRow(10).getCell(2).getNumericCellValue();
			int status3 = (int)sheet.getRow(11).getCell(2).getNumericCellValue();
			int status4 = (int)sheet.getRow(12).getCell(2).getNumericCellValue();
			int status5 = (int)sheet.getRow(13).getCell(2).getNumericCellValue();
			int status6 = (int)sheet.getRow(14).getCell(2).getNumericCellValue();
			
			runUnitCheck.put("status1", status1);
			runUnitCheck.put("status2", status2);
			runUnitCheck.put("status3", status3);
			runUnitCheck.put("status4", status4);
			runUnitCheck.put("status5", status5);
			runUnitCheck.put("status6", status6);
			Double a = 1.0;
			Double b = 1.0;
			if(status2 == 1 ||status3 == 1||status6 == 1){
				a = 0.0;
			}else if(status1 == 1 && status4 == 1 && status5 == 1){
				b = 0.5 ;
			}else if(status1 == 1 && status4 == 0 && status5 == 1){
				b = 0.5 ;                                         
			}else if(status1 == 1 && status4 == 1 && status5 == 0){
				b = 0.5 ;                                         
			}else if(status1 == 0 && status4 == 1 && status5 == 1){
				b = 0.5 ;                                         
			}else if(status1 == 0 && status4 == 0 && status5 == 1){
				b = 0.5 ;                                         
			}else if(status1 == 0 && status4 == 1 && status5 == 0){
				b = 0.75 ;                                        
			}else if(status1 == 1 && status4 == 0 && status5 == 0){
				b = 0.5 ;
			}
			runUnitCheck.put("runQuality", a * b);
			
            fis.close();
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
			return runUnitCheck;
	}
	
	private Map<String,Object> saveMonthStationCheck() {

		Map<String,Object> stationCheck =  new HashMap<>();
		
        try {
        	 FileInputStream fis = new FileInputStream(fileToBeRead);
        	 XSSFWorkbook workbook = new XSSFWorkbook(fis);
             XSSFSheet sheet = workbook.getSheet("sheet5");
             XSSFRow row = sheet.getRow(1);
             XSSFRow row3 = sheet.getRow(3);
             XSSFRow row4 = sheet.getRow(4);
             XSSFRow row5 = sheet.getRow(5);
             
             XSSFFormulaEvaluator eval=new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
     		 eval.evaluateFormulaCell(row.getCell(1));  
     		 eval.evaluateFormulaCell(row.getCell(2));  
     		 eval.evaluateFormulaCell(row3.getCell(3));  
     		 eval.evaluateFormulaCell(row4.getCell(3)); 
     		 eval.evaluateFormulaCell(row5.getCell(3));
             
             
             stationCheck.put("runUnitId", row.getCell(5).getStringCellValue());
             
             SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
             sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
             Date checkTime;
			try {
				checkTime = sdf.parse(row.getCell(4).getStringCellValue());
				stationCheck.put("checkMonth", checkTime);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
			stationCheck.put("stationCode",row.getCell(1).getStringCellValue());
			row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
			stationCheck.put("stationName",row.getCell(2).getStringCellValue());
			stationCheck.put("transferStatus",sheet.getRow(3).getCell(3).getNumericCellValue());
			stationCheck.put("validateStatus",(int)(sheet.getRow(4).getCell(3).getNumericCellValue()));
			stationCheck.put("scoresStatus",sheet.getRow(5).getCell(3).getNumericCellValue());
			double coefficient = sheet.getRow(6).getCell(1).getNumericCellValue();
//			if(sheet.getRow(3).getCell(3).getNumericCellValue()<0.85 || (int)(sheet.getRow(4).getCell(3).getNumericCellValue())<27 || 
//					sheet.getRow(5).getCell(3).getNumericCellValue()<80){
//				coefficient=0.0;
//			}
			stationCheck.put("coefficient", coefficient);
            fis.close();
	        } catch (FileNotFoundException e) {
	            e.printStackTrace();
	        } catch (IOException e) {
	            e.printStackTrace();
	        }
			return stationCheck;
	}
	
	
	
}
